Store data permanently on the hard disk, opposed to temporarily in memory.
Space
Space on the hard disk is much cheaper and much more scalable than memory.
Separation
Separate the data from code so it can be shared by many different applications.
File system vs database system
A database system is an intermediate layer between the file system and the applications. The databases implement a vast amount of convenient features that make it easier to define, write and read the data. Database systems is an interface to model, read and write data, which is easier to use than writing and reading raw files. The databases may even be more efficient and scalable than the raw file system. When the number of data increases, handling it without a database system requires writing increasingly redundant and complex functions. Database systems solve this by abstracting away redundant procedures, and handles operations on the data even if it all does not fit in memory. Database systems allow us to operate on the data declaratively, instead of imperatively: for large data sets, this is magnitudes easier to understand and reason about. Database systems can effectivize the performance of these operations and implement security features and support constraints such as data types.
Database
A collection of data presented in a specific format.
Document database
Text document storage. Efficient searching within large amounts of text.
Key-value stores
Graph database
Focus on graphs. Enables effective discovery of paths between nodes; alternatively it is good at handling relations between entities.
Relational database
Focus on tabular data: collections of tuples. "A relational database is a collection of tables".
Security and access control
Databases implement authentication and access control to control who can read and write what data.
Integrity
Databases implement data constraints and ensure that data conform, so invalid data does not ocurr in the database.
Database system
A program used to interact with a database. Used to define, interact and maintain data and control the access to the information.
Relational databases advantages & disadvantages
Most used. Rigid structure: data is in a form exactly as described by the model. Easy to specify metadata/schema for the data. Rigid structuring of the data leads to highly efficient methods to read and write. Many possibilities for security control. Can support vast amounts of data, and scales better than the file system. Disadvantages: rigid. Not for unstructured data(such data must be stored as blob and cannot in general take advantage of database features). For example, markup, large text files, audio and video: could use document databases instead. Graph databases are better for querying/finding relations/paths between entities. Big blobs of data might be better to just use files, since the database system is then just a useless middle layer.
Query language
A query language is used to send queries to the database: these are requests to read data. The database handles the queries and retrieves, processes and returns the data in the way the query specified. Declarative: explain what instead of how(imperative). Database system decides how to retrieve the data. Most DQL are declarative.
Data manipulation language
Data definition language
Database connectivity interface
SQL
SQL is a query language, DML and DDL used in the relational SQL database systems.
Data vs information
We can have data, but no information. In order to get information from data, we need to know how to interpret the data. For example, we might need to know what the data measures, unit of measurement and how it relates to the context and other data. In other words, we need metadata that tell us how to turn the data back into information.
Information
Information are real facts. Information is equivalent to data and metadata that tells us how and what the data concerns: how the data can be turned back into information.
Domain
A domain is a real environment that we want to model.
Data modelling
In data modelling, we want to find a good data model for a given domain. We must identify the type of information we care about in the domain, and create a data model that can represent this type of information. In relational databases, this amounts to determining which tables and columns to include, and the relations between them (deciding the database schema).
Data models
There are in fact several popular types of data models, for example UML, ER, RM. The domain might be large and complex, with hundreds or thousands of tables; therefore it could be important to have a good strategy that systematically turns the domain into a model.
Data modelling language
(Not to be confused for data definition language) A data modelling language is a language used to model domains: it can also be described as being a terminology to talk about data and relations. After creating a data model using a data modelling language, the model can be systematically turned into a database schema (supposed that the data modelling language and database system are compatible).
UML
ORM
ER
OWL
Relational model
Relational data modelling
Table
Also called relation. Has name, columns and rows.
Column
Has a name and constraints such as type.
Column type
Schema
Row
Primary key
Foreign key
Constraint
Entity-relational model
SQL
SQL is a query language, DML and DDL used in the relational SQL database systems.
CREATE TABLE
SELECT FROM
SELECT name
FROM Items
WHERE id = 1
SELECT DISTINCT C.containerId
FROM Containers AS C JOIN Contains AS I ON C.id = I.containerId
WHERE I.itemId = 560
SELECT FROM
SELECT name
FROM Items
WHERE id = 1
SELECT DISTINCT C.containerId
FROM Containers AS C JOIN Contains AS I ON C.id = I.containerId
WHERE I.itemId = 560
GROUP BY
JOIN
SELECT C.containerId
FROM Containers AS C JOIN Contains AS I ON C.id = I.containerId
WHERE I.itemId = 560